﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Web;
using TTQT.TTQT_Code;

/// <summary>
/// Summary description for SanPham
/// </summary>
public class TinTucDAO
{
    public string NewsId = "";
    public string CategoryId = "";
    public string Title = "";
    public string ImagePath = "";
    public string Summary = "";
    public string Content = "";
    public string IsActive = "1";
    public string Hotcat = "1";
    public string Hotsite = "1";
    public string Createdate = "";
    public string PortalId = "";
    public string UserId = "";
    public string Status = "1";
    public string ExSummary = "";
    public string ImagePath2 = "";

    //Cach nay eo' nhanh bang su dung sqlDataAdapter
    public DataTable GetDataTableFromDataReader1(IDataReader dataReader)
    {
        DataTable schemaTable = dataReader.GetSchemaTable();
        DataTable resultTable = new DataTable();

        foreach (DataRow dataRow in schemaTable.Rows)
        {
            DataColumn dataColumn = new DataColumn();
            dataColumn.ColumnName = dataRow["ColumnName"].ToString();
            dataColumn.DataType = Type.GetType(dataRow["DataType"].ToString());
            dataColumn.ReadOnly = (bool)dataRow["IsReadOnly"];
            dataColumn.AutoIncrement = (bool)dataRow["IsAutoIncrement"];
            dataColumn.Unique = (bool)dataRow["IsUnique"];

            resultTable.Columns.Add(dataColumn);
        }

        while (dataReader.Read())
        {
            DataRow dataRow = resultTable.NewRow();
            for (int i = 0; i < resultTable.Columns.Count - 1; i++)
            {
                dataRow[i] = dataReader[i];
            }
            resultTable.Rows.Add(dataRow);
        }

        return resultTable;
    }
    //xong
    public int insert()
    {
        int kq = 0;
        SqlConnection conn = new SqlConnection(TTQTCommon.DBConnectionString);
        SqlCommand cmd = new SqlCommand("News_add", conn);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@categoryid", CategoryId);
        cmd.Parameters.AddWithValue("@title", Title);
        cmd.Parameters.AddWithValue("@imagepath", ImagePath);
        cmd.Parameters.AddWithValue("@summary", Summary);
        cmd.Parameters.AddWithValue("@content", Content);
        cmd.Parameters.AddWithValue("@isactive", IsActive);
        cmd.Parameters.AddWithValue("@hotcat", Hotcat);
        cmd.Parameters.AddWithValue("@hotsite", Hotsite);
        cmd.Parameters.AddWithValue("@portalId", PortalId);
        cmd.Parameters.AddWithValue("@userId", UserId);
        cmd.Parameters.AddWithValue("@Exsummary", ExSummary);
        cmd.Parameters.AddWithValue("@imagepath2", ImagePath2);
        try
        {
            conn.Open();
            kq = TTQTCommon.ToInt32(cmd.ExecuteScalar(), 0);
            NewsId = kq.ToString();
        }
        catch (Exception exxx)
        { }
        finally
        {
            if (conn.State == ConnectionState.Open) conn.Close();
            conn.Dispose();
            cmd.Dispose();
        }
        return kq;
    }
    //XONG
    public string getById_json(string _id)
    {
        NewsId = _id;
        string kq = "";
        ArrayList infos = new ArrayList();
        if(getById())
        {
            TinTucInfo info = new TinTucInfo();
            info.NewsId = NewsId;
            info.CategoryId = CategoryId;
            info.Title = Title;
            info.ImagePath = ImagePath;
            info.Summary = Summary;
            info.Content = Content;
            info.IsActive = IsActive;
            info.IsActive = IsActive.ToLower().Equals("true") ? "1" : "0";
            info.Hotcat = Hotcat;
            info.Hotsite = Hotsite;
            info.Createdate = Createdate;
            info.PortalId = PortalId;
            info.UserId = UserId;
            infos.Add(info);
        }
        kq = new System.Web.Script.Serialization.JavaScriptSerializer().Serialize(infos);
        return kq;//kkkkkkkkkkkkkkkkkkkllllllllllllllllll
    }
    public bool getById()
    {
        bool kq = false;
        SqlConnection conn = new SqlConnection(TTQTCommon.DBConnectionString);
        SqlCommand comm = new SqlCommand("News_Getbyid", conn);
        comm.CommandType = CommandType.StoredProcedure;
        comm.Parameters.AddWithValue("@NewsId", NewsId);
        try
        {
            conn.Open();
            SqlDataReader dr = comm.ExecuteReader();
            if (dr.Read())
            {
                kq = true;
                NewsId = TTQTCommon.ToString(dr["NewsId"]);
                CategoryId = TTQTCommon.ToString(dr["CategoryId"]);
                Title = TTQTCommon.ToString(dr["Title"]);
                ImagePath = TTQTCommon.ToString(dr["ImagePath"]);
                Summary = TTQTCommon.ToString(dr["Summary"]);
                Content = TTQTCommon.ToString(dr["Content"]);
                IsActive = TTQTCommon.ToString(dr["IsActive"]);IsActive = IsActive.ToLower().Equals("true") ? "1" : "0";
                Hotcat = TTQTCommon.ToString(dr["Hotcat"]);
                Hotsite = TTQTCommon.ToString(dr["Hotsite"]);
                Createdate = TTQTCommon.ToString(dr["Createdate"]);
                PortalId = TTQTCommon.ToString(dr["PortalId"]);
                UserId = TTQTCommon.ToString(dr["UserId"]);

            }
        }
        catch (Exception ex)
        {
        }
        finally
        {
            if (conn.State == ConnectionState.Open) conn.Close();
            conn.Dispose();
            comm.Dispose();
        }
        return kq;
    }
    public int update()
    {
        int kq = 0;
        SqlConnection conn = new SqlConnection(TTQTCommon.DBConnectionString);
        SqlCommand cmd = new SqlCommand("News_Update", conn);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@NewsId", NewsId);
        cmd.Parameters.AddWithValue("@categoryid", CategoryId);
        cmd.Parameters.AddWithValue("@title", Title);
        cmd.Parameters.AddWithValue("@imagepath", ImagePath);
        cmd.Parameters.AddWithValue("@summary", Summary);
        cmd.Parameters.AddWithValue("@content", Content);
        cmd.Parameters.AddWithValue("@isactive", IsActive);
        cmd.Parameters.AddWithValue("@hotcat", Hotcat);
        cmd.Parameters.AddWithValue("@hotsite", Hotsite);
        cmd.Parameters.AddWithValue("@portalId", PortalId);
        cmd.Parameters.AddWithValue("@userId", UserId);
        cmd.Parameters.AddWithValue("@Exsummary", ExSummary);
        cmd.Parameters.AddWithValue("@imagepath2", ImagePath2);
        try
        {
            conn.Open();
            kq = TTQTCommon.ToInt32(cmd.ExecuteNonQuery(), 0);
        }
        catch (Exception exxx)
        { }
        finally
        {
            if (conn.State == ConnectionState.Open) conn.Close();
            conn.Dispose();
            cmd.Dispose();
        }
        return kq;
    }
    public int delete()
    {
        int kq = 0;
        SqlConnection conn = new SqlConnection(TTQTCommon.DBConnectionString);
        SqlCommand cmd = new SqlCommand("News_Delete", conn);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@NewsId", NewsId);
        try
        {
            conn.Open();
            kq = TTQTCommon.ToInt32(cmd.ExecuteNonQuery(), 0);
        }
        catch (Exception exxx)
        { }
        finally
        {
            if (conn.State == ConnectionState.Open) conn.Close();
            conn.Dispose();
            cmd.Dispose();
        }
        return kq;
    }
    //public DataTable timKiem(string keyWord, string nhomId, string loaiId, string giaTien)
    //{
    //    if (keyWord.Equals("Nhập từ khóa"))
    //    {
    //        keyWord = "";
    //    }
    //    DataTable result = null;
    //    string sql =
    //        "select SanPham.id,NhomId,LoaiId,Tenhang,SanPham_Nhom.[Name] as TenNhom,TomLuoc,NoiDung,GiaBanThamKhao,PicUrl,userName,convert(varchar(10),NgayNhap,103) as ngayNhap,hangmoi,active,del from SanPham left join SanPham_Nhom on sanpham.NhomId=SanPham_nhom.id";
    //    string whereClause = " where 1=1";
    //    if (!string.IsNullOrEmpty(keyWord))
    //    {
    //        whereClause += " and (tenHang like '%" + keyWord + "%' or tomLuoc like '%" + keyWord + "%' or noiDung like '%" + keyWord + "%')";
    //    }
    //    whereClause += " and (nhomId=" + nhomId + " or -1=" + nhomId + ")";
    //    whereClause += " and (loaiId=" + loaiId + " or -1=" + loaiId + ")";
    //    if (!giaTien.Equals("-1"))
    //    {
    //        whereClause += " and GiaBanThamKhao " + giaTien;
    //    }
    //    sql += whereClause;

    //    SqlConnection conn = new SqlConnection(TTQTCommon.DBConnectionString);
    //    SqlCommand comm = new SqlCommand(sql, conn);

    //    comm.CommandType = CommandType.Text;
    //    try
    //    {
    //        conn.Open();
    //        result = new DataTable();
    //        SqlDataAdapter da = new SqlDataAdapter(comm);
    //        da.Fill(result);
    //    }
    //    catch (Exception exx)
    //    {
    //        // Writing log methods here.
    //    }
    //    finally
    //    {
    //        if (conn.State == ConnectionState.Open) conn.Close();
    //        conn.Dispose();
    //        comm.Dispose();
    //    }
    //    return result;

    //}
    public DataTable TinTuc_GetByNhomId(string nhomId, string iActive)
    {
        DataTable result = null;

        SqlConnection conn = new SqlConnection(TTQTCommon.DBConnectionString);
        SqlCommand comm = new SqlCommand("News_GetByNhomID", conn);

        comm.CommandType = CommandType.StoredProcedure;
        comm.Parameters.Clear();
        comm.Parameters.AddWithValue("@id", nhomId);
        comm.Parameters.AddWithValue("@IsActive", iActive);
        try
        {
            conn.Open();
            result = new DataTable();
            SqlDataAdapter da = new SqlDataAdapter(comm);
            da.Fill(result);
        }
        catch (Exception exx)
        {
            // Writing log methods here.
        }
        finally
        {
            if (conn.State == ConnectionState.Open) conn.Close();
            conn.Dispose();
            comm.Dispose();
        }
        return result;
    }
    public DataTable tinTuc_GetTopForRSS(string nhomId, string iActive)
    {
        DataTable result = null;

        SqlConnection conn = new SqlConnection(TTQTCommon.DBConnectionString);
        SqlCommand comm = new SqlCommand("tinTuc_GetTopForRSS", conn);
        comm.CommandType = CommandType.StoredProcedure;
        comm.Parameters.Clear();
        comm.Parameters.AddWithValue("@id", nhomId);
        comm.Parameters.AddWithValue("@IsActive", iActive);
        try
        {
            conn.Open();
            result = new DataTable();
            SqlDataAdapter da = new SqlDataAdapter(comm);
            da.Fill(result);
        }
        catch (Exception exx)
        {
            // Writing log methods here.
        }
        finally
        {
            if (conn.State == ConnectionState.Open) conn.Close();
            conn.Dispose();
            comm.Dispose();
        }
        return result;
    }
    public PagingOpbject news_GetByNhomId_Paging(string nhomId, string iActive, int pageSize, int currentPage)
    {
        PagingOpbject kq = new PagingOpbject();
        DataTable result = null;
        SqlConnection conn = new SqlConnection(TTQTCommon.DBConnectionString);
        SqlCommand comm = new SqlCommand("TinTuc_GetByNhomId_Paging", conn);

        comm.CommandType = CommandType.StoredProcedure;
        comm.Parameters.Clear();
        comm.Parameters.Add("@NhomId", SqlDbType.Int, 4).Value = nhomId;
        comm.Parameters.Add("@isActive", SqlDbType.Int, 4).Value = iActive;

        comm.Parameters.Add("@PageSize", SqlDbType.Int, 4).Value = pageSize;
        comm.Parameters.Add("@CurrentPage", SqlDbType.Int, 4).Value = currentPage;
        comm.Parameters.Add("@ItemCount", SqlDbType.Int).Direction = ParameterDirection.Output;
        try
        {
            conn.Open();
            result = new DataTable();
            SqlDataAdapter da = new SqlDataAdapter(comm);
            da.Fill(result);
            kq.tableReturn = result;
            kq.totalRecords = Convert.ToInt32(comm.Parameters["@ItemCount"].Value);
        }
        catch (Exception exx)
        {
            // Writing log methods here.
        }
        finally
        {
            if (conn.State == ConnectionState.Open) conn.Close();
            conn.Dispose();
            comm.Dispose();
        }
        return kq;
    }
    public DataTable tintuc_nhom_getByID(string id)
    {
        DataTable result = null;

        SqlConnection conn = new SqlConnection(TTQTCommon.DBConnectionString);
        SqlCommand comm = new SqlCommand("tintuc_nhom_getByID", conn);
        comm.CommandType = CommandType.StoredProcedure;
        comm.Parameters.Clear();
        comm.Parameters.Add("@id", SqlDbType.Int, 4).Value = id;

        try
        {
            conn.Open();
            result = new DataTable();
            SqlDataAdapter da = new SqlDataAdapter(comm);
            da.Fill(result);
        }
        catch (Exception exx)
        {
            // Writing log methods here.
        }
        finally
        {
            if (conn.State == ConnectionState.Open) conn.Close();
            conn.Dispose();
            comm.Dispose();
        }
        return result;
    }
    #region Cac phuong thuc cho quan tri
    public DataTable tinTuc_Nhom_GetAllForAdmin()
    {
        DataTable result = null;

        SqlConnection conn = new SqlConnection(TTQTCommon.DBConnectionString);
        SqlCommand comm = new SqlCommand("TinTuc_Nhom_GetAllForAdmin", conn);
        
        comm.CommandType = CommandType.StoredProcedure;
        comm.Parameters.Clear();
        try
        {
            conn.Open();
            result = new DataTable();
            SqlDataAdapter da = new SqlDataAdapter(comm);
            da.Fill(result);
        }
        catch (Exception exx)
        {
            // Writing log methods here.
        }
        finally
        {
            if (conn.State == ConnectionState.Open) conn.Close();
            conn.Dispose();
            comm.Dispose();
        }
        return result;
    }
    public string tinTuc_GetByNhomId_Json(string nhomId, string active)
    {
        string kq = "";
        DataTable tb = tinTuc_GetByNhomAndIsActive(nhomId, active);
        ArrayList infos = new ArrayList();
        foreach (DataRow r in tb.Rows)
        {
            TinTucInfo info = new TinTucInfo();
            info.NewsId = r["NewsId"].ToString();
            info.CategoryId = r["CategoryId"].ToString();
            info.Title = r["Title"].ToString();
            info.ImagePath = r["ImagePath"].ToString();
            info.Summary = r["Summary"].ToString();
            info.Createdate = r["Createdate"].ToString();
            infos.Add(info);
        }
        kq = new System.Web.Script.Serialization.JavaScriptSerializer().Serialize(infos);
        return kq;//kkkkkkkkkkkkkkkkkkkllllllllllllllllll
    }
    /// <summary>
    /// Dung trong quan tri. Lay danh sach tin theo nhom, active
    /// </summary>
    public DataTable tinTuc_GetByNhomAndIsActive(string categoryId,string isActive)
    {
        DataTable result = null;

        SqlConnection conn = new SqlConnection(TTQTCommon.DBConnectionString);
        SqlCommand comm = new SqlCommand("TinTuc_GetByNhomAndIsActive", conn);
        comm.CommandType = CommandType.StoredProcedure;
        comm.Parameters.Clear();
        comm.Parameters.Add("@CategoryId", SqlDbType.Int, 4).Value = categoryId;
        comm.Parameters.Add("@isActive", SqlDbType.Int, 4).Value = isActive;
        try
        {
            conn.Open();
            result = new DataTable();
            SqlDataAdapter da = new SqlDataAdapter(comm);
            da.Fill(result);
        }
        catch (Exception exx)
        {
            // Writing log methods here.
        }
        finally
        {
            if (conn.State == ConnectionState.Open) conn.Close();
            conn.Dispose();
            comm.Dispose();
        }
        return result;
    }
    public string news_GetTopByCategory_Json(string num, string categoryId)
    {
        string kq = "";
        DataTable tb = News_GetTopByCategory(num, categoryId);
        ArrayList infos = new ArrayList();
        foreach (DataRow r in tb.Rows)
        {
            TinTucInfo info = new TinTucInfo();
            info.NewsId = r["NewsId"].ToString();
            info.CategoryId = r["CategoryId"].ToString();
            info.Title = r["Title"].ToString();
            info.ImagePath = r["ImagePath"].ToString();
            info.Summary = r["Summary"].ToString();
            info.Createdate = r["Createdate"].ToString();
            infos.Add(info);
        }
        kq = new System.Web.Script.Serialization.JavaScriptSerializer().Serialize(infos);
        return kq;//kkkkkkkkkkkkkkkkkkkllllllllllllllllll
    }
    /// <summary>
    /// Dung khi hien thi tin tuc cho khach hang
    /// </summary>
    /// <param name="num">So luong ban ghi can lay</param>
    /// <returns>DataTable de bind len man hinh xem</returns>
    public DataTable News_GetTopByCategory(string num, string categoryId)
    {
        DataTable result = null;

        SqlConnection conn = new SqlConnection(TTQTCommon.DBConnectionString);
        SqlCommand comm = new SqlCommand("News_GetTopByCategory", conn);
        comm.CommandType = CommandType.StoredProcedure;
        comm.Parameters.Clear();
        comm.Parameters.Add("@num", SqlDbType.Int, 4).Value = num;
        comm.Parameters.Add("@categoryId", SqlDbType.Int, 4).Value = categoryId;
        try
        {
            conn.Open();
            result = new DataTable();
            SqlDataAdapter da = new SqlDataAdapter(comm);
            da.Fill(result);
        }
        catch (Exception exx)
        {
            // Writing log methods here.
        }
        finally
        {
            if (conn.State == ConnectionState.Open) conn.Close();
            conn.Dispose();
            comm.Dispose();
        }
        return result;
    }

    /// <summary>
    /// Lay cac tin khac phia duoi muc tin
    /// </summary>
    /// <param name="num"></param>
    /// <param name="numToSkip"></param>
    /// <param name="categoryId"></param>
    /// <returns></returns>
    public DataTable News_GetOtherByCategory(string num,string numToSkip, string categoryId)
    {
        DataTable result = null;

        SqlConnection conn = new SqlConnection(TTQTCommon.DBConnectionString);
        SqlCommand comm = new SqlCommand("News_GetOtherByCategory", conn);
        comm.CommandType = CommandType.StoredProcedure;
        comm.Parameters.Clear();
        comm.Parameters.Add("@num", SqlDbType.Int, 4).Value = num;
        comm.Parameters.Add("@numtoSkip", SqlDbType.Int, 4).Value = numToSkip;
        comm.Parameters.Add("@categoryId", SqlDbType.Int, 4).Value = categoryId;
        
        try
        {
            conn.Open();
            result = new DataTable();
            SqlDataAdapter da = new SqlDataAdapter(comm);
            da.Fill(result);
        }
        catch (Exception exx)
        {
            // Writing log methods here.
        }
        finally
        {
            if (conn.State == ConnectionState.Open) conn.Close();
            conn.Dispose();
            comm.Dispose();
        }
        return result;
    }
    /// <summary>
    /// Lấy tốp num các tin có cùng mục sau tin hiện tại
    /// </summary>
    /// <param name="num"></param>
    /// <param name="newsId"></param>
    /// <returns></returns>
    public string News_getOtherByNewsId_Json(string num, string newsId)
    {
        string kq = "";
        DataTable tb = News_getOtherByNewsId(num, newsId);
        ArrayList infos = new ArrayList();
        foreach (DataRow r in tb.Rows)
        {
            TinTucInfo info = new TinTucInfo();
            info.NewsId = r["NewsId"].ToString();
            info.CategoryId = r["CategoryId"].ToString();
            info.Title = r["Title"].ToString();
            info.ImagePath = r["ImagePath"].ToString();
            info.Summary = r["Summary"].ToString();
            info.Createdate = r["Createdate"].ToString();
            infos.Add(info);
        }
        kq = new System.Web.Script.Serialization.JavaScriptSerializer().Serialize(infos);
        return kq;//kkkkkkkkkkkkkkkkkkkllllllllllllllllll
    }
    public DataTable News_getOtherByNewsId(string num, string newsId)
    {
        DataTable result = null;

        SqlConnection conn = new SqlConnection(TTQTCommon.DBConnectionString);
        SqlCommand comm = new SqlCommand("News_getOtherByNewsId", conn);
        comm.CommandType = CommandType.StoredProcedure;
        comm.Parameters.Clear();
        comm.Parameters.Add("@num", SqlDbType.Int, 4).Value = num;
        comm.Parameters.Add("@NewsId", SqlDbType.Int, 4).Value = newsId;
        try
        {
            conn.Open();
            result = new DataTable();
            SqlDataAdapter da = new SqlDataAdapter(comm);
            da.Fill(result);
        }
        catch (Exception exx)
        {
            // Writing log methods here.
        }
        finally
        {
            if (conn.State == ConnectionState.Open) conn.Close();
            conn.Dispose();
            comm.Dispose();
        }
        return result;
    }
    #endregion
}
